package database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;




import dataPO.*;
import databaseInterface.CustomerManageInterface;

public class CustomerManage implements CustomerManageInterface{
	/**
	 * @param args
	 */
	
	Statement stmt;
	DatabaseConnection db= new DatabaseConnection();
  
	public  void connect (){
        
         stmt =db.connect();
    }
    
	public void insert (CustomerPO cus){
    	 String insert ="insert into customer values ('"+cus.getName()+"','"+cus.getPassword()+"','"+Format.toString3(cus.getDenomOfEquiCoupons())+"','"+Format.toString(cus.getNumberOfEquiCoupons())+"','"+Format.toString2(cus.getDiscountOfDisCouponsPO())+"','"+Format.toString(cus.getNumberOfDisCouponsPO())+"',"+cus.getPoint()+",'"+Format.toString4(cus.getBookCollection())+"')";
         try {
			stmt.executeUpdate(insert);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    
   }

	public void delete (String name ){
    	
    	String delete="delete from customer where cusName = '"+name+"'" ;
    	try {
			stmt.executeUpdate(delete);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    
	public  void update(CustomerPO cus,String name){
    	
    	String update ="update customer set cusName ='"+cus.getName()+"', password = '"+cus.getPassword()+"', equiTicket = '"+Format.toString3(cus.getDenomOfEquiCoupons())+"', equiNum = '"+Format.toString(cus.getNumberOfEquiCoupons())+"', discTicket = '"+Format.toString2(cus.getDiscountOfDisCouponsPO())+"', discNum = '"+Format.toString(cus.getNumberOfDisCouponsPO())+"', point = "+cus.getPoint()+", bookISBN = '"+Format.toString4(cus.getBookCollection())+"' where cusName = '"+name+"'" ;
        try {
			stmt.executeUpdate(update);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}    
    }
    
	public ArrayList<CustomerPO> query (String name ){
    	String query = "select * from customer where cusName = '"+name+"'";
    	ArrayList<CustomerPO> cuslist = new ArrayList<CustomerPO> ();
     	try {
			ResultSet rs= stmt.executeQuery(query);
		
    	
    	while(rs.next()){
    		CustomerPO cus= toCusPO(rs);
    	    cuslist.add(cus);
    	
    	}
    	} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	return cuslist;	
    }
	public ArrayList<CustomerPO> query ( ){
    	String query = "select * from customer ";
    	ArrayList<CustomerPO> cuslist = new ArrayList<CustomerPO> ();
     	try {
			ResultSet rs= stmt.executeQuery(query);
		
    	
    	while(rs.next()){
    		CustomerPO cus= toCusPO(rs);
    	    cuslist.add(cus);
    	
    	}
    	} catch (SQLException e) {
		 	// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	return cuslist;	
    }
	
	

	
    private CustomerPO toCusPO(ResultSet rs) {
	// TODO Auto-generated method stub
    	CustomerPO cus = new CustomerPO();
    	try {
    		cus.setName(rs.getString(1));
    		cus.setPassword( rs.getString(2));
			 cus.setPoint(rs.getDouble("point"));
			 String str1 = rs.getString("equiTicket");
			 cus.setDenomOfEquiCoupons(Format.tolist3(str1));
			 String str2 = rs.getString("equiNum");
			 cus.setNumberOfEquiCoupons(Format.tolist(str2));
			 String str3 = rs.getString("discTicket");
			 cus.setDiscountOfDisCouponsPO(Format.tolist2(str3));
			 String str4 = rs.getString("discNum");
			 cus.setNumberOfDisCouponsPO(Format.tolist(str4));
			 cus.setBookCollection(Format.tolist4(rs.getString("bookISBN")));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	return cus;
}
    
    public boolean exist(String name){
    	ArrayList<CustomerPO> a=  query(name);
    	if(a.isEmpty())  return false;
    	else return true ;
    }
    
    public  void close(){
      db.close();
    }
   

   


  
}
